********************************************************************************
* This the main file to generate regression tables on
* 1. cash holding and acquisition
* 2. cash holding and liquidity cost
* This file calls acquisition_cash.do, cash_demand_HP_filter.do and cash_demand_BK_filter.do
********************************************************************************


clear

cd "/Users/yuzhu/Desktop/codes_and_data_final_submisson/micro_level_evidence"  //enter working directory. You should change this line to the folder in your computer
use "compustat2021.dta"
set matsize 11000
*===============================================================
* drop observations whose 
*===============================================================
sort gvkey fyear
drop if capx==. | aqc==. | sppe ==.
drop if fyear < 1971
drop if fyear>2018
drop if loc == "CAN"
by gvkey: gen id = 1 if fyear[_n]==fyear[_n-1]
replace id=0 if id==.
by gvkey: gen repid = sum(id)
drop if repid>0
* exclude financial firms
drop if sic>6000 & sic<6500
drop if sic<1000 | sic>9000 
*drop if sic >=4900 & sic<=4999

merge m:1 fyear using "liquidity_costs.dta" // merge with cpi data
drop _merge
*merge m:1 fyear using "deflator.dta"       // merge with business deflator
*drop _merge
*merge m:1 fyear using "inflation_by_CPI.dta" // merge with final version of CPI

*rename deflator_bv bva

drop if fyear < 1971
drop if at<0
******************************************
*generate higher level industry code
******************************************

/*gen bigsic = 1 if sic<1000
replace bigsic=2 if sic>=1000&sic<1500
replace bigsic=3 if sic>=1500&sic<1800
replace bigsic=4 if sic>=2000&sic<4000
replace bigsic=5 if sic>=4000&sic<5000
replace bigsic=6 if sic>=5000&sic<5200
replace bigsic=7 if sic>=5200&sic<6000
replace bigsic=8 if sic>=6000&sic<6800
replace bigsic=9 if sic>=7000&sic<9000
replace bigsic=10 if sic>=9000&sic<9730
replace bigsic=11 if sic>=9900&sic<10000*/
replace cpi = cpi/100
gen bigsic = floor(sic/100)
*by gvkey: egen totalat = mean(at)
*by gvkey: gen idquantile = 1 if _n==1
*xtile ptile = totalat if idquantile==1,nq(100)
*by gvkey: replace ptile = ptile[1]
*gen fconstr=1 if ptile<=30
*replace fconstr=0 if ptile>=70
*replace fconstr=2 if ptile<70&ptile>30


*************************************************
* Normalize variables by cpi
*************************************************
gen at_r = at/cpi    // _r means normalized to 1971 prices
gen at_ppi = at/bva    // _r means normalized to 1971 prices
gen at_cpi_final = at/cpi_final
gen che_r = log(che/cpi)
gen ch_r = ch/cpi
gen aqc_r = log(aqc/cpi)
gen aqc_ppi = log(aqc/bva)

gen sale_r = log(sale/cpi)
gen logat_r = log(at_r)
gen logat_ppi = log(at_ppi)
gen logat_cpi_final = log(at_cpi_final)
gen capx_r = log(capx/cpi)
gen capxv_r = capxv/cpi
gen sppe_r = sppe/cpi
gen ebit_r = log(ebit/cpi)
gen ebit_ppi = log(ebit/bva)
gen ebit_cpi_final = log(ebit/cpi_final)
gen inflation_log = log(1+inflation)
replace cpi_inflation_final = cpi_inflation_final-1
*******************************************************
* normalize variable by total asset
*******************************************************
gen cheat = log(che/at)
gen capxat = log(capx/at)
gen lratio = (dltt+dlc)/seq
gen lratio_l = dltt/seq
gen lratio_c = dlc/seq
gen lratio_a = (at - seq)/seq /* alternative calculation of leverage ratio: total liability/shareholders equity*/
gen capxvat = capxv/at  //capital expenditure on sppe
gen logat =log(at)
gen chat = ch/at
gen aqcat = log(aqc/at)
gen sppeat = sppe/at
gen saleat=sale/at
gen ebitat= log(ebit/at)
bysort bigsic fyear: egen avg_at = mean(at) 
gen relative_size_log = log(at/avg_at)
gen relative_size = at/avg_at

bysort fyear state: egen state_che = mean(che)
bysort fyear state: egen state_at= mean(at)
gen cheat_state = log(state_che/state_at)
gen sppe_at   = log(sppe/at)
gen sppe_dummy = 1 if sppe>0
replace sppe_dummy = 0 if sppe_dummy==.




*replace lratio = lratio/10

gen chratio = ch/che // ratio of cash to cash and cash equivalent: higher means more liquid
gen aqccapx = log(aqc/(capx+aqc)) //fraction of acquisition 
gen pshare  =  log(sppe/(sppe+aqc))
gen aqcdummy = 1 if aqc>0
replace aqcdummy = 0 if aqc<=0

***********************************************************************
* startup: id for first three years to approximate start-ups
* exceed_id: 1 if the aqc exceeds assets for some period
***********************************************************************
xtset gvkey fyear
gen pre_start_id = l3.cheat 
gen new=1 if pre_start_id ==.
by gvkey: egen max_aqcat = max(aqcat)
gen exceed_id = 1 if max_aqcat>0
drop pre_start_id 


xtset gvkey fyear

bysort gvkey:gen lcheat = l.cheat




do "acquisition_cash_log.do" // generate tables for acquisition on cash holding
do "cash_demand_HP_filter_log.do" // generate tables for cash holding and cash demand with HP filter
do "cash_demand_BK_filter_log.do" // generate tables for cash holding and cash demand with BK filter
